Sub 一键完成结算()
t = Timer

Dim dic As Object, mAry, mRow As Long
Set dic = CreateObject("scripting.dictionary")
With Worksheets("卡数据")
      mRow = .Cells(.Rows.Count, 1).End(3).Row
      mAry = .[a2].Resize(mRow - 1, 2)
End With
For i = 1 To UBound(mAry, 1)
      dic("" & mAry(i, 1)) = mAry(i, 2)
Next i
With Worksheets("出账明细")
      mRow = .Cells(.Rows.Count, 1).End(3).Row
      mAry = .[a2].Resize(mRow - 1, 1)
      For i = 1 To UBound(mAry, 1)
            If dic.exists("" & mAry(i, 1)) Then
                  mAry(i, 1) = dic("" & mAry(i, 1))
            Else
                  mAry(i, 1) = "#异常数据"
           End If
      Next i
      .[c2].Resize(UBound(mAry, 1), 1) = mAry
End With


With Worksheets("折扣")
      mRow = .Cells(.Rows.Count, 1).End(3).Row
      mAry = .[a2].Resize(mRow - 1, 2)
End With
For i = 1 To UBound(mAry, 1)
      dic("" & mAry(i, 1)) = mAry(i, 2)
Next i
With Worksheets("出账明细")
      mRow = .Cells(.Rows.Count, 1).End(3).Row
      mAry = .[c2].Resize(mRow - 1, 1)
      For i = 1 To UBound(mAry, 1)
            If dic.exists("" & mAry(i, 1)) Then
                  mAry(i, 1) = dic("" & mAry(i, 1))
            Else
                  mAry(i, 1) = 0
           End If
      Next i
      .[e2].Resize(UBound(mAry, 1), 1) = mAry
End With


Dim strCell As String
Dim nLastUseRow As Long
Dim nLastRcdRow As Long

Dim Rng As Range

Set Rng = Range("A" + CStr(Cells.SpecialCells(xlCellTypeLastCell).Row + 1)).End(xlUp)
nLastRcdRow = Rng.Row ' 得到行号

Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]/R2C9"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & nLastRcdRow)
Range("F2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & nLastRcdRow)

Columns("C:F").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "出账明细!R1C3:R1048576C6", Version:=6).CreatePivotTable TableDestination:= _
    "收入汇总!R4C1", TableName:="数据透视表2", DefaultVersion:=6
Sheets("收入汇总").Select
Cells(4, 1).Select
With ActiveSheet.PivotTables("数据透视表2").PivotFields("公司名称")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("数据透视表2").AddDataField ActiveSheet.PivotTables("数据透视表2" _
    ).PivotFields("收入"), "求和项:收入", xlSum
Columns("B:B").Select
Selection.Style = "Comma"
Range("B13").Select
ActiveSheet.PivotTables("数据透视表2").PivotFields("公司名称").AutoSort xlDescending, _
    "求和项:收入", ActiveSheet.PivotTables("数据透视表2").PivotColumnAxis.PivotLines(1), 1
Columns("B:B").Select
Selection.Style = "Comma"

MsgBox "操作已完成!" + (Chr(13)) + "用时:" + Format(Timer - t, "0.00秒")


End Sub
